<?php defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );

/**
 * Hockey extension for Joomleague
 * @version $Id: playersranking.php 111 2008-04-09 18:11:29Z julien.vonthron $
 * @package JlgHockeyExtension
 * @copyright Copyright (C) 2007 Julien Vonthron. All rights reserved.
 * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.php
 * JlgHockeyExtension is free software and parts of it may contain or be derived from the
 * GNU General Public License or other free or open source software licenses.
 * 
 * contact: julien.vonthron[at]gmail.com 
 *   
 * Joomleague is a League Management and Prediction Game for Joomla!
 * Homepage: http://www.joomleague.de 
 *  
 */
?>

<?php
	  
	function showPlayersRanking() 
	{
	  global $joomleague, $database, $folder, $overall_config;
	  global $mosConfig_absolute_path, $mosConfig_live_site, $Itemid;
	  $divisionId = intval(mosGetParam( $_REQUEST , 'div', 0) );
	  $divLevel = intval(mosGetParam( $_REQUEST , 'divLevel', 0) );
	  $display_mode = intval(mosGetParam( $_REQUEST , 'display', 0) );
	  
	  $playerTable_config=array_merge($overall_config, $joomleague->getTemplateConfig("show_hockey_players_rank.tpl"));
	  
	  $limit = intval($playerTable_config['row_count'],10);
	  
	  if ( (!$divisionId) && $divLevel) { 
	    $divs = $joomleague->getDivisionsId($divLevel); 
	    
	  }
	  else {
	    $divs = Array( $divisionId );
	    if ($display_mode) $limit = 0;
	  }
	  
	  $tables=Array();
	  $tables_goalies=Array();
	  foreach ($divs as $d) {
	    $results = getPlayersRanking($joomleague, $d, $playerTable_config);
	    if ( count($results) ) {
	       $tables[$d] = ( $limit>0 ? array_slice( $results, 0, $limit ): $results );   
	    }
	    $results = getGoaliesRanking($joomleague, $d, $playerTable_config);
	    if ( count($results) ) {
	      //print_r($results);exit;
	       $tables_goalies[$d] = ( $limit>0 ? array_slice( $results, 0, $limit ): $results );   
	    }
	  }
	  $project = $joomleague;
	  require (JL_TEMPLATE_PATH.'/tmpl/show_players_ranking.tpl.php');
	}
	
	/**
	 * returns an array of players ordered by goals+assists
	 *
	 * @param object $project
	 * @param int $divisionId
	 * @param array $config
	 * @param string $sort
	 * @return array of players info object
	 */
	function getPlayersRanking( &$project, $divisionId = 0, &$config, $sort = 'points' ) {
		//return false;
	  global $database;
	  $evid1 = $config['event_goals'];
	  $evid2 = $config['event_assists'];
	  
	  $query_SELECT = "SELECT SUM(me.event_sum) as ev, me.project_id,
	                  p.id, p.firstname, p.lastname,
	                  t.name as team_name, t.short_name as team_short_name, t.middle_name as team_middle_name,
	                  pt.id as playertoolid, pt.team_id, pt.position_id,
	                  teamtool.id as teamtoolid";
	
	  $query_FROM = " FROM #__joomleague_match_events AS me
	                  INNER JOIN #__joomleague_matches AS m ON m.match_id = me.match_id
	                  INNER JOIN #__joomleague_players AS p ON p.id = me.player_id
	                  INNER JOIN #__joomleague_playertool AS pt ON me.player_id = pt.player_id AND pt.project_id = $project->id
	                  INNER JOIN #__joomleague_teams AS t ON (pt.team_id = t.id AND (t.id = m.matchpart1 OR t.id = m.matchpart2))
	                  INNER JOIN #__joomleague_team_joomleague AS teamtool ON teamtool.team_id = t.id AND teamtool.project_id = $project->id
	                 ";
	  
	  $query_WHERE =" WHERE me.project_id = $project->id";
	  $query_GROUP =" GROUP BY me.player_id, me.project_id ORDER BY NULL";
	
	  if ( $divisionId ) {
	    $query_FROM .=" INNER JOIN #__joomleague_divisions AS jldiv ON jldiv.id = teamtool.division_id";
	    $query_WHERE .=" AND (jldiv.id = $divisionId OR jldiv.parent_id = $divisionId)";
	  }
	  
	  $query_WHERE_EV =" AND me.event_type_id = $evid1";
	  $database->setQuery($query_SELECT.$query_FROM.$query_WHERE.$query_WHERE_EV.$query_GROUP);
	  if ( !$result1 = $database->loadObjectList() ) {
	//    echo "<pre>";
	//    echo $database->getErrorMsg();
	//    echo "</pre>";
	//    return;
	  }
	
	  $query_WHERE_EV =" AND me.event_type_id = $evid2";                            
	  $database->setQuery($query_SELECT.$query_FROM.$query_WHERE.$query_WHERE_EV.$query_GROUP);
	  if ( !$result2 = $database->loadObjectList() ) {
	//    echo "<pre>";
	//    echo $database->getErrorMsg();
	//    echo "</pre>";
	//    return;
	  }
	  
	  $sum = Array();
	  foreach ( $result1 as $r ) {
	    if ( !(array_key_exists( $r->id, $sum ) ) ) {
	      $sum[$r->id]=get_object_vars($r);
	      $sum[$r->id]['ev1']=$r->ev;
	      $sum[$r->id]['ev2']=0;
	    }
	    else $sum[$r->id]['ev1'] += $r->ev;
	  }
	  foreach ( $result2 as $r ) {
	    if ( !(array_key_exists( $r->id, $sum ) ) ) {
	      $sum[$r->id]=get_object_vars($r);
	      $sum[$r->id]['ev1']=0;
	      $sum[$r->id]['ev2']=$r->ev;
	    }
	    else $sum[$r->id]['ev2'] += $r->ev;
	  }
	  
	  if (!function_exists ('cmpPlayersPoints')) {
	    function cmpPlayersPoints ($a, $b) {
	      return ($a['ev1']+$a['ev2']) < ($b['ev1']+$b['ev2']);
	    }
	  }
	  
	  usort($sum, 'cmpPlayersPoints');
	  return ($sum);
	}
	
	/**
	 * returns an array of goalies ordered by goals against average
	 *
	 * @param object $project
	 * @param int $divisionId
	 * @param array $config
	 * @param string $sort
	 * @return array of goalies info object
	 */
	function getGoaliesRanking( &$project, $divisionId = 0, &$config, $sort = 'points' ) 
	{
	  //return false;
		global $project_stats;
	  
	  // we use $project_stats for caching. This way, we don't have to repeat the same queries 
	  // for all divisions
	  if ( !isset($project_stats) ) 
	  {		  
			$playersGAARank = getGAARanking( $project->id, 'ASC', 0, 0 );
			
		  // get array of playertool ids
		  $playertool_ids = array();
		  foreach ( $playersGAARank as $p)
		  {
		  	$playertool_ids[] = $p->playertool_id;
		  }
		  
		  $players_so = getShutoutsPlayersStats( $project->id );
		  	  		  
		  $players_info = getPlayersInfo( $playertool_ids );
		  
		  $project_stats = array();
		  foreach ( $playersGAARank as $p )
		  {
		  	$player = $players_info[$p->playertool_id];
		  	$player->id = $p->player_id;
		  	$player->gaa = $p->p;
		  	$player->games_played = $p->games_played;
		  	$player->goals_allowed = $p->goals_allowed; 
		  	$player->shutouts = $players_so[$p->player_id]->shutouts;
		  	$project_stats[] = $player;
		  }		  
//		  print_r($project_stats);
	  }
	  
	  // filter the results for the specified division
	  if ( $divisionId )
	  {
	  	$results = array();
	  	foreach ( $project_stats as $p )
	  	{
	  		if ( $p->division_id == $divisionId || $p->parent_id == $divisionId ) {
	  			$results[] = $p;
	  		}
	  	}
	  	return $results;
	  }
	  else {
      return ($project_stats);
	  }
	}
 
  /**
   * returns array of object (total stat / player_id)
   *
   * @param int $project_id
   * @param int $event_id
   * @param int $limit
   * @param int $limitstart
   * @return objects array
   */
  function getTotalStatRanking($project_id, $event_id, $limit=10, $limitstart=0)
  {
  	global $database;
  	if ( !$project_id ) return null;
  	
    $query = 'SELECT SUM(me.event_sum) AS p, 
                      pt.id as playertool_id,
                      pt.player_id
              FROM #__joomleague_match_events AS me
              INNER JOIN #__joomleague_playertool AS pt USING (project_id, player_id)
              WHERE me.project_id =  '.$project_id
            . ' AND me.event_type_id =  '.$event_id
            . ' GROUP BY me.player_id, pt.team_id ORDER BY p DESC ';
    if ( $limit ) {
      $query .= ' LIMIT ' . $limitstart . ', ' . $limit;
    }
	  $database->setQuery($query);
	  
	  return $database->loadObjectList('playertool_id');
  }
  
  /**
   * returns array of object (per game stat / player_id)
   *
   * @param int $project_id
   * @param int $event_id
   * @param int $division
   * @param string $order
   * @param int $limit
   * @param int $limitstart
   * @return objects array
   */
  function getGAARanking($project_id, $order = 'DESC', $limit=10, $limitstart=0)
  {
  	global $database, $overall_config;
  	if ( !$project_id ) return null;
  	
	  $posGoalie = $overall_config['goalie_position_id'];
	  
  	// sub request for goals allowed
    $sub_ga = 'SELECT mp.player_id, mp.team_id, SUM(me.event_sum) AS total_event
  			                  FROM #__joomleague_match_events AS me 
  			                  INNER JOIN #__joomleague_match_players AS mp USING (match_id, player_id)
  			                  WHERE  me.event_type_id = '.$overall_config['event_goals_allowed'] 
  			                 .' AND me.project_id = '.$project_id	                          
	                       .' GROUP BY mp.player_id, mp.team_id' ;
	                       
  	// sub request for games played as a goalie
	  $sub_games_played = "SELECT  mp.player_id, mp.team_id, COUNT(*) AS games_played
	                        FROM #__joomleague_match_players AS mp
	                        INNER JOIN #__joomleague_matches AS m USING (match_id)
	                        WHERE m.project_id = $project_id	
	                          AND mp.position_id = $posGoalie	                          
	                        GROUP BY mp.player_id, mp.team_id";
  			                    
  	$query = 'SELECT (total_event / games_played) AS p, games_played, total_event AS goals_allowed,  	             
  	              pt.id AS playertool_id, pt.player_id, pt.team_id
                  FROM #__joomleague_playertool AS pt
                  INNER JOIN ('.$sub_ga.') AS se USING (player_id, team_id)
                  INNER JOIN ('.$sub_games_played.') AS gp USING (player_id, team_id)';
                  
    $query_where  = ' WHERE games_played > 0 AND pt.project_id = '.$project_id;
    $query_where .= ' GROUP BY pt.player_id, pt.team_id';
    $query_where .= ' ORDER BY (total_event/games_played) ' . $order;
    if ( $limit ) {
      $query_where .= ' LIMIT ' . $limitstart . ', ' . $limit;
    }
            
	  $database->setQuery($query.$query_where);
//	  echo $database->getQuery();
    return $database->loadObjectList( );
  }
  
  /**
   * returns array of object (per game stat / player_id) in decreasing order
   *
   * @param int $project_id
   * @param int $event_id
   * @param int $limit
   * @param int $limitstart
   * @return objects array
   */
  function getRatioStatRanking($project_id, $dividend_event_id, $divisor_event_id, $limit=10, $limitstart=0)
  {
  	global $database;
  	if ( !$project_id ) return null;
  	
  	// sub request for dividend event total
    $sub_dividend = 'SELECT  mp.player_id, SUM(me.event_sum) AS dividend
  			                  FROM #__joomleague_match_events AS me 
  			                  INNER JOIN #__joomleague_match_players AS mp USING (match_id, player_id)
  			                  WHERE  me.event_type_id = '.$dividend_event_id 
  			                 .' AND me.project_id = '.$project_id	                          
	                       .' GROUP BY mp.player_id' ;
	                       
  	// sub request for divisor event total
    $sub_divisor = 'SELECT  mp.player_id, SUM(me.event_sum) AS divisor
  			                  FROM #__joomleague_match_events AS me 
  			                  INNER JOIN #__joomleague_match_players AS mp USING (match_id, player_id)
  			                  WHERE  me.event_type_id = '.$divisor_event_id 
  			                 .' AND me.project_id = '.$project_id	                          
	                       .' GROUP BY mp.player_id' ;
  			                    
  	$query = 'SELECT (dividend / divisor) AS p, pt.id AS player_id
                  FROM #__joomleague_playertool AS pt
                  INNER JOIN ('.$sub_dividend.') AS sd USING (player_id)
                  INNER JOIN ('.$sub_divisor.') AS sdv USING (player_id)
                  WHERE divisor > 0 AND pt.project_id = '.$project_id 
              . ' ORDER BY (dividend / divisor) DESC';
    if ( $limit ) {
      $query .= ' LIMIT ' . $limitstart . ', ' . $limit;
    }
                  
	  $database->setQuery($query);
	  
    return $database->loadObjectList();
  }

  function getShutoutsPlayersStats( $project_id )
  {
    //return false;
  	global $database, $overall_config;
    
    $ev_ga = $overall_config['event_goals_allowed'];
    $posGoalie = $overall_config['goalie_position_id'];
    
    $sub_shutouts = ' SELECT mp.player_id, COUNT(*) as shutouts
                      FROM #__joomleague_match_players AS mp
                      INNER JOIN #__joomleague_matches AS m USING (match_id)
                      LEFT JOIN #__joomleague_match_events AS me ON ( me.match_id = mp.match_id AND me.player_id = mp.player_id)
                      WHERE m.project_id = ' . $project_id
                  . ' AND mp.position_id = ' . $posGoalie
                  . ' AND me.event_sum IS NULL
                      GROUP BY mp.player_id ';

    $database->setQuery( $sub_shutouts );
    $result1 = $database->loadObjectList('player_id');
    
    return $result1;   
  }
  
  
  /**
   * returns an array of players info objects
   *
   * @param array int $playertool_ids
   * @return array
   */
  function getPlayersInfo( $playertool_ids )
  {  	
    global $database;
    
    $query = ' SELECT pt.id AS ptid, pt.team_id, pt.player_id, '
                  . ' p.firstname, p.lastname, '
                  . ' t.name AS team_name, t.short_name AS team_short, '
                  . ' tt.id as teamtoolid, '
                  . ' jldiv.id as division_id, jldiv.parent_id
                  FROM #__joomleague_playertool AS pt
                  INNER JOIN #__joomleague_players AS p ON (p.id = pt.player_id)
                  INNER JOIN #__joomleague_teams AS t ON (t.id = pt.team_id)
                  INNER JOIN #__joomleague_team_joomleague AS tt ON (tt.team_id = pt.team_id AND tt.project_id = pt.project_id)
                  INNER JOIN #__joomleague_divisions AS jldiv ON (jldiv.id = tt.division_id )
                  WHERE pt.id IN (' . implode( ", ", $playertool_ids ). ') ';
    $database->setQuery($query);
    
    return $database->loadObjectList('ptid');
  }
  
?>
